package com.ischoolbar.programmer.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.ischoolbar.programmer.model.Clazz;
import com.ischoolbar.programmer.model.Page;
import com.ischoolbar.programmer.model.Student;
import com.ischoolbar.programmer.util.StringUtil;

public class StudentDao extends BaseDao {

	public boolean addStudent(Student student){
		String sql = "insert into s_student values(null,'"+student.getSn()+"','"+student.getName();
		sql += "','" + student.getPassword() + "','" + student.getClazzId();
		sql += "','" + student.getSex() + "','" + student.getMobile();
		sql += "','" + student.getQq()+ "',null)";
		return update(sql);
	}
	
	public boolean editStudent(Student student) {
		// TODO Auto-generated method stub
		String sql = "update s_student set name= '"+student.getName()+"'";
		sql += ",sex = '"+student.getSex() +"'";
		sql += ",mobile = '"+student.getMobile() +"'";
		sql += ",qq = '" +student.getQq() +"'";
		sql += ",clazz_id = '" +student.getClazzId()+"'";
		sql += " where id = '"+ student.getId() +"'";
		return update(sql);
	}
	
	public boolean setStudentPhoto(Student student) {
		// TODO Auto-generated method stub
		String sql = "update s_student set photo= ?";
		Connection connection = getConnection();
		try {
			PreparedStatement prepareStatement = connection.prepareStatement(sql);
			prepareStatement.setBinaryStream(1, student.getPhoto());;
			return prepareStatement.executeUpdate(sql) > 0;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return update(sql);
	}
	
	public List<Student> getStudentList(Student student,Page page){
		List<Student> ret = new ArrayList<Student>();
		String sql = "select * from s_student ";
		if(!StringUtil.isEmpty(student.getName())){
			sql += "and name like '%" + student.getName() +"%'";
		}
		if(student.getClazzId() != 0){
			sql += " and clazz_id = " +student.getClazzId() ;
		}
		sql += " limit " + page.getStart() +","+page.getPageSize();
		ResultSet resultSet = query(sql.replaceFirst("and", "where"));
		try {
			while(resultSet.next()){
				student = new Student();
				student.setId(resultSet.getInt("id"));
				student.setClazzId(resultSet.getInt("clazz_id"));
				student.setMobile(resultSet.getString("mobile"));
				student.setName(resultSet.getString("name"));
				student.setPassword(resultSet.getString("password"));
				student.setPhoto(resultSet.getBinaryStream("photo"));
				student.setQq(resultSet.getString("qq"));
				student.setSex(resultSet.getString("sex"));
				student.setSn(resultSet.getString("sn"));
				ret.add(student);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ret;
		
		
	}
	
	
	public int getStudentListTotal(Student student){
		int total = 0;
		String sql = "select count(*) as total from s_student ";
		if(!StringUtil.isEmpty(student.getName())){
			sql += "and name like '%" + student.getName() +"%'";
		}
		if(student.getClazzId() != 0){
			sql += "and clazz_id = " +student.getClazzId() ;
		}
	
		ResultSet resultSet = query(sql.replaceFirst("and", "where"));

		try {
			while(resultSet.next()){
				total = resultSet.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;
		
	}
	
	public Student getStudent(int id){
		
		String sql = "select * from s_student where id = " + id;
		Student student = null;
		ResultSet resultSet = query(sql);
		try {
			if(resultSet.next()){
				student = new Student();
				student.setId(resultSet.getInt("id"));
				student.setClazzId(resultSet.getInt("clazz_id"));
				student.setMobile(resultSet.getString("mobile"));
				student.setName(resultSet.getString("name"));
				student.setPassword(resultSet.getString("password"));
				student.setPhoto(resultSet.getBinaryStream("photo"));
				student.setQq(resultSet.getString("qq"));
				student.setSex(resultSet.getString("sex"));
				student.setSn(resultSet.getString("sn"));
				return student;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return student;
	}
	
	
	
	
}
